﻿use master
if exists (select * from sysdatabase where name='TuDienOnLine'
			drop database TuDienOnLine)
go
create database TuDienOnLine on primary
(	
 name='TDOL_data', 
 filename='D:\datasql\TuDienOnLine\TuDienOnLine.mdf'
)
log on
(	
 name='TDOL_log',
 filename='D:\Datasql\TuDienOnLine\TuDienOnLine.ldf'
)
use TuDienOnLine
Go
Create table TuVung
(
	MaTV int identity(1,1) primary key,	
	Tu nvarchar(100) unique,	
	TheLoai nvarchar(50),
	NgonNgu nvarchar(20) not null
)

--drop table tuvung
Create table LoaiTu
(
	MaTu int identity (1,1) primary key,
	TenLoaiTu nvarchar(20)not null,
)
Create table CTLoaiTu
(
	MaTV int not null,
	MaTu int not null,
	NghiaAnh varchar(100),
	PhienAm nvarchar(50),
	NghiaViet nvarchar(100) ,
	primary key(MaTV,MaTu)
)
--alter table TheLoai add Mabh int primary key
Create table TaiKhoan
(
	MaTK int identity(1,1) primary key,
	TenND varchar(50) unique,
	MatKhau varchar(50) not null,
	HoTen nvarchar(50) not null,
	email varchar(100),
	QuocGia nvarchar(35),
	NgayThamGia datetime,
	ChucNang nvarchar(20),
	TinhTrang nvarchar(20),
	BaoMat nvarchar(20)
)
create table BangTinNhan
(
	MaTin int identity(1,1) primary key,
	MaTK int,
	MaTK1 int,
	ThuNhan nvarchar(4000),
	NgayNhan datetime,
	ThuGui nvarchar(4000),
	NgayGui datetime,
	TinhTrang nvarchar(20)
)
create table KetBan
(
	MaKB int identity(1,1) primary key,
	TenQuanHe nvarchar(20) not null,
	MaTK int,
	MaTK1 int,
	LoiNhan nvarchar(4000),
	TinhTrang nvarchar(20)
)

Create table BaiHoc
(
	Mabh int identity(1,1) primary key ,
	MaTK int,
	TenBH nvarchar(50),
	DuongDan varchar(50),
	HinhAnh varchar(50),
	TinhTrang nvarchar(10),
	NgayDang datetime
)
Create table DanhSachTu
(
	MaTV int,
	MaTK int,
	GhiChu nvarchar(400),
	Primary key(MaTV, MaTK)
)
Create table QuangCao
(
	MaQC int identity(1,1) primary key, 
	SoTT int,
	NgayBatDau varchar(10),
	NgayKetThuc varchar(10),
	Hinh varchar(100),
	ChiPhi float,
	Link varchar(100),
	TrangThai nvarchar(10)
)
create table HoiDap
(
	MaHD int identity(1,1)primary key,
	MaTK int,
	CauHoi nvarchar(4000),
	NgayHoi datetime,
	TraLoi nvarchar(4000),
	NguoiTLoi nvarchar(50),
	NgayTLoi datetime
)

--------------------
alter table CTLoaiTu
add constraint FK_TuVung_CTLoaiTu foreign key (MaTV)
references TuVung(MaTV)
on update cascade
----------------
alter table BaiHoc
add constraint FK_BaiHoc_TaiKhoan foreign key (MaTK)
references TaiKHoan(MaTK)
on update cascade
----------------------
alter table HoiDap
add constraint FK_HoiDap_TaiKhoan foreign key (MaTK)
references TaiKHoan(MaTK)
on update cascade
----------------------
alter table CTLoaiTu
add constraint FK_LoaiTu_CTLoaiTu foreign key (MaTu)
references LoaiTu(MaTu)
on update cascade
------------------
alter table DanhsachTu
add constraint FK_TuVung_DanhsachTu foreign key (MaTV)
references TuVung(MaTV)
on update cascade
---------------
alter table DanhsachTu
add constraint FK_TaiKhoan_DanhsachTu foreign key (MaTK)
references TaiKhoan(MaTK)
on update cascade
--------------------------
alter table BangTinNhan
add constraint FK_BangTinNhan_TaiKhoan foreign key (MaTK)
references taiKhoan(MaTK)
on update cascade
--------------------------
alter table KetBan
add constraint FK_TaiKhoan_KetBan foreign key (MaTK)
references TaiKhoan(MaTK)
on update cascade

SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
   -- lệnh 1
   -- lệnh 2
  -- ...
COMMIT
END TRY
BEGIN CATCH
   ROLLBACK --rollback de quay lui lai transaction.
   DECLARE @ErrorMessage VARCHAR(2000)
   SELECT @ErrorMessage = 'Lỗi: ' + ERROR_MESSAGE()
   RAISERROR(@ErrorMessage, 16, 1)
END

-----------
CREATE PROC For_TransTest  
AS 
BEGIN 
BEGIN TRY              
 BEGIN TRANSACTION updatestaff;  
UPDATE aspnet.staff SET fk_department = 30  
 WHERE pk_staff = 971;  
UPDATE aspnet.staff SET fk_department = null 
WHERE pk_staff = 971;  
COMMIT TRANSACTION updatestaff;  
PRINT 'commited';  
END TRY  
BEGIN CATCH  
PRINT 'rollback ne';  
ROLLBACK TRAN updatestaff;   
END CATCH  
END 
